﻿using System;
using System.Collections.Generic;
using System.Text;
using combit.ListLabel16.DataProviders;
using MySql.Data.MySqlClient;
using System.Data;
using System.Runtime.Serialization;
using System.Security.Permissions;
using System.Globalization;

namespace combit.ListLabel16.DataProviders
{
    /// <summary>
    /// Provider for MySql, see http://www.mysql.de/products/connector/
    /// Tested with version 6.0.3
    /// </summary>
    [Serializable]
    public sealed class MySqlConnectionDataProvider : DbConnectionDataProvider, ISerializable
    {
        private MySqlConnectionDataProvider()
            : base()
        {
        }

        private MySqlConnectionDataProvider(SerializationInfo info, StreamingContext context)
            : base(info, context)
        {
            if (info.GetInt32("MySqlConnectionDataProvider.Version") >= 1)
            {
                Connection = new MySqlConnection();
                Connection.ConnectionString = info.GetString("ConnectionString");
                SupportedElementTypes = (DbConnectionElementTypes)info.GetInt32("SupportedElementTypes");
                Provider.CancelBeforeClose = false;
            }
        }

        public MySqlConnectionDataProvider(MySqlConnection connection)
        {
            Connection = connection as IDbConnection;
            SupportedElementTypes = DbConnectionElementTypes.Table;
            Provider.CancelBeforeClose = false;
        }

        public DbConnectionElementTypes SupportedElementTypes
        {
            get;
            set;
        }

        protected override void Init()
        {
            if (Initialized)
                return;

            List<String> passedRelationNames = new List<string>();
            List<String> passedTables = new List<string>();
            List<String> passedSchemas = new List<string>();
            List<String> excludedOwners = new List<string>();
            excludedOwners.Add("mysql");
            excludedOwners.Add("information_schema");
            if (Connection.State != ConnectionState.Open)
                Connection.Open();
            MySqlCommand cmd;
            try
            {
                DataTable views = null;
                DataTable tables = null;
                if ((SupportedElementTypes & DbConnectionElementTypes.Table) != 0)
                {
                    tables = (Connection as MySqlConnection).GetSchema("Tables");
                }
                if ((SupportedElementTypes & DbConnectionElementTypes.View) != 0)
                {
                    views = (Connection as MySqlConnection).GetSchema("Views");
                }
                Connection.Close();

                for (int pass = 0; pass < 2; pass++)
                {
                    DataTable currentTable;
                    switch (pass)
                    {
                        case 0:
                            currentTable = views;
                            break;
                        case 1:
                            currentTable = tables;
                            break;
                        default:
                            currentTable = null;
                            break;
                    }

                    if (currentTable == null)
                        continue;

                    foreach (DataRow dr in currentTable.Rows)
                    {
                        string schema = dr["TABLE_SCHEMA"].ToString();

                        if (excludedOwners.Contains(schema))
                            continue;

                        // Get db from connection string
                        MySqlConnection connection = (MySqlConnection)Connection;
                        string database = connection.Database;

                        // If no database is specified in the connectionString, get all tables of all schemas,
                        // otherwise just get the tables of the specified db
                        if ((schema != database) && !String.IsNullOrEmpty(database))
                            continue;

                        // No schema specified, add them to list, to build relations
                        if (!passedSchemas.Contains(schema))
                        {
                            passedSchemas.Add(schema);
                        }

                        string name = dr["TABLE_NAME"].ToString();

                        ICloneable cloneable = (ICloneable)Connection;
                        MySqlConnection newConnection = (MySqlConnection)cloneable.Clone();
                        cmd = new MySqlCommand("Select * From " + (String.IsNullOrEmpty(schema) ? name + "" : schema + "." + name) + "", newConnection);
                        Provider.AddCommand(cmd as IDbCommand, name, "`{0}`", "?{0}");
                        passedTables.Add(name);
                    }
                }

                foreach (string schema in passedSchemas)
                {
                    string commandText = String.Format(CultureInfo.InvariantCulture, "SELECT K.`TABLE_NAME`, K.`COLUMN_NAME`, K.`REFERENCED_TABLE_NAME`, K.`REFERENCED_COLUMN_NAME` FROM information_schema.KEY_COLUMN_USAGE K WHERE `TABLE_SCHEMA`='{0}'", schema);
                    cmd = new MySqlCommand(commandText, Connection as MySqlConnection);
                    Connection.Open();
                    MySqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                    while (reader.Read())
                    {
                        if (!reader.IsDBNull(2) && !reader.IsDBNull(3))
                        {
                            string childColumnName = reader.GetString(1).ToString();
                            string parentColumnName = reader.GetString(3).ToString();
                            string childTableName = reader.GetString(0).ToString();

                            if (!passedTables.Contains(childTableName))
                                continue;

                            string parentTableName = reader.GetString(2).ToString();

                            if (!passedTables.Contains(parentTableName))
                                continue;

                            string relName = parentTableName + "2" + childTableName;
                            int relationIndex = 1;
                            string formatString = relName + "{0}";

                            while (passedRelationNames.Contains(relName))
                            {
                                relName = String.Format(CultureInfo.InvariantCulture, formatString, relationIndex);
                                relationIndex++;
                            }
                            passedRelationNames.Add(relName);
                            Provider.AddRelation(relName, parentTableName, childTableName, parentColumnName, childColumnName);
                        }
                    }
                    reader.Close();
                }
            }
            finally
            {
                Connection.Close();
                Initialized = true;
            }
        }
        #region ISerializable Members
        [SecurityPermissionAttribute(SecurityAction.Demand, SerializationFormatter = true)]
        public override void GetObjectData(SerializationInfo info, StreamingContext context)
        {
            base.GetObjectData(info, context);
            info.AddValue("MySqlConnectionDataProvider.Version", 1);
            info.AddValue("ConnectionString", Connection.ConnectionString);
            info.AddValue("SupportedElementTypes", (int)SupportedElementTypes);
        }

        #endregion
    }
}